Learn SQL Quickly: A Beginner’s Guide to Learning SQL, Even If You’re New to Databases (Crash Course With Hands-On Project Book 4) by Code Quickly

Author:Code Quickly [Quickly, Code]
Language: eng
Format: epub
Published: 2020-11-10T00:00:00+00:00

How to pick a logical order?

This relies upon the query that will make use of the index – you must pick an index that permits:

Your WHERE conditionals to effectively look-up data.

Your rows to be sorted and grouped in a way beneficial to the query.

Your Joins to be efficient.

Index S electivity

Index selectivity is the ratio of the number of distinct indexed values (the cardinality) to the total number of rows in the table (#T).

It ranges from 1/#T to 1. A unique index has a selectivity of 1, which is as good as it gets.

Using the phonebook example: an index of (first_name, last_name) might be less effective than (last_name, first_name) because first names are much less distinct when compared to last_names, meaning it can narrow down less results.

Therefore, if order is not important between two columns, choose the most selective (narrows down to less records) first.

Range Queries

Similar to what we have just explained with the prefix rule, the moment you use a range query on a column in your index, you have gone as far as you can utilizing this index.

If you issued a range query such as:

SELECT * FROM phonebook WHERE last_name LIKE ‘J%’ AND first_name =’Michael’;ADD INDEX (last_name, first_name, phone_number)

This would utilize the first part (last_name) of our index, allowing us to quickly satisfy the range conditional and find all of the rows with the last_name beginning with ‘J.’ However after this, there is no way our B-Tree can be further utilized to quickly filter on first_name.

If you are utilizing an index for range queries, try to ensure the column you are performing the range over is ordered last within the index. Similarly, you cannot use an index to perform range queries on two columns for the points already mentioned.

Index Condition Pushdown

That being said, there is a technique called Index Condition Pushdown that can help us with the issue outlined above.

Index pushdowns essentially allow us to push index conditions down to the database engine so that MySQL does not have to return irrelevant rows that would only be filtered out later.

This means that in some cases we can still use an index past a range condition. It would be best to experiment and test to see the results for yourself. Regardless of the situation, MySQL will be at its most effective if you are able to leave range columns towards the end of your index. Index Condition Pushdowns should only be used when you cannot find a viable alternative.

Typically, we can only use the leftmost prefix of the index.

l You may need to have indexes on the same columns in different orders depending on your queries.

l Try to use as many columns as possible up to the first range of the query – after a range, no other index column can be used. So put the index that is likely to be ranged right at the end.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.